drop proc [sp_GetThreads]
go
CREATE PROCEDURE [dbo].[sp_GetThreads]
    @SectionId int,
    @AuthorId int,
    @ReplierId int,
    @ThreadStatus int,
    @ThreadReleaseStatus int,
    @OrderField int,
    @PageIndex int,
    @PageSize int,
    @TotalRecords int output
AS

DECLARE @tableName NVARCHAR(128)
DECLARE @topCount int
DECLARE @returnFields NVARCHAR(256)
DECLARE @selectTopFields NVARCHAR(128)
DECLARE @dynamicConditions NVARCHAR(256)
DECLARE @selectCountSql NVARCHAR(1024) 
DECLARE @selectSql NVARCHAR(1024)
DECLARE @orderFields NVARCHAR(128)
DECLARE @disOrderFields NVARCHAR(128)
declare @variableDeclarations NVARCHAR(2000)
DECLARE @parameterString1 NVARCHAR(2000)
DECLARE @parameterString2 NVARCHAR(2000)
DECLARE @parameterString3 NVARCHAR(2000)
DECLARE @parameterString4 NVARCHAR(2000)

DECLARE @orderFieldName NVARCHAR(32)

-- set @tableName.
set @tableName = N'tb_Threads'

-- set @returnFields.
set @returnFields = N'
t.EntityId,
t.Subject,
t.CreateDate,
t.UpdateDate,
t.StickDate,
t.AuthorId,
t.Author,
t.GroupId,
t.SectionId,
t.TotalPosts,
t.TotalViews,
t.ThreadStatus,
t.MostRecentReplierId,
t.MostRecentreplierName,
t.ThreadMarks,
t.ThreadReleaseStatus
'

-- init the local variables.
set @dynamicConditions = N''
set @variableDeclarations = N''
set @parameterString1 = N''
set @parameterString2 = N''
set @parameterString3 = N''
set @parameterString4 = N''

-- set @topCount.
set @topCount = (@pageIndex + 1) * @pageSize

-- set @orderFieldName.
if @OrderField = 1
	set @orderFieldName = N't.TotalViews'
else if @OrderField = 2
	set @orderFieldName = N't.TotalPosts'
else
	set @orderFieldName = N't.UpdateDate'

-- set @selectTopFields. 
-- Notes: The select top fields must include the entityId and the order fields.
set @selectTopFields = N't.EntityId,' + N't.StickDate,' + @orderFieldName

-- set @orderFields and @disOrderFields.
set @orderFields = N't.StickDate desc,' + @orderFieldName + N' desc'
set @disOrderFields = N't.StickDate asc,' + @orderFieldName + N' asc'

-- set the other variables according with the business logic.
if not @AuthorId is null and @AuthorId > 0
begin
	set @variableDeclarations = @variableDeclarations + N'DECLARE @AuthorId int; set @AuthorId = ' + CAST(@AuthorId AS NVARCHAR(32)) + N';'
	set @dynamicConditions = @dynamicConditions + N' AND AuthorId = @AuthorId'
	set @parameterString1 = @parameterString1 + N'@AuthorId int, '
	set @parameterString2 = @parameterString2 + N'@AuthorId, '
	set @parameterString3 = @parameterString3 + N',@AuthorId int'
	set @parameterString4 = @parameterString4 + N',@AuthorId'
end
if not @ThreadStatus is null and @ThreadStatus >= 0
begin
	set @variableDeclarations = @variableDeclarations + N'DECLARE @ThreadStatus int; set @ThreadStatus = ' + CAST(@ThreadStatus AS NVARCHAR(32)) + N';'
	set @dynamicConditions = @dynamicConditions + N' AND ThreadStatus = @ThreadStatus'
	set @parameterString1 = @parameterString1 + N'@ThreadStatus int, '
	set @parameterString2 = @parameterString2 + N'@ThreadStatus, '
	set @parameterString3 = @parameterString3 + N',@ThreadStatus int'
	set @parameterString4 = @parameterString4 + N',@ThreadStatus'
end
if not @ThreadReleaseStatus is null and @ThreadReleaseStatus >= 0
begin
	set @variableDeclarations = @variableDeclarations + N'DECLARE @ThreadReleaseStatus int; set @ThreadReleaseStatus = ' + CAST(@ThreadReleaseStatus AS NVARCHAR(32)) + N';'
	set @dynamicConditions = @dynamicConditions + N' AND ThreadReleaseStatus = @ThreadReleaseStatus'
	set @parameterString1 = @parameterString1 + N'@ThreadReleaseStatus int, '
	set @parameterString2 = @parameterString2 + N'@ThreadReleaseStatus, '
	set @parameterString3 = @parameterString3 + N',@ThreadReleaseStatus int'
	set @parameterString4 = @parameterString4 + N',@ThreadReleaseStatus'
end

-- set @selectSql and @selectCountSql.
if @SectionId is null or @SectionId <= 0
begin
	if @ReplierId is null or @ReplierId <= 0
	begin
		set @selectSql = N'SELECT TOP ' + CAST(@topCount AS NVARCHAR(32)) + ' ' + @selectTopFields + N' FROM tb_Threads t INNER JOIN tb_Sections s ON t.SectionId = s.EntityId INNER JOIN tb_Groups g ON t.GroupId = g.EntityId WHERE s.Enabled = 1 AND g.Enabled = 1' + @dynamicConditions + ' ORDER BY ' + @orderFields
		set @selectCountSql = N'SELECT t.EntityId FROM tb_Threads t INNER JOIN tb_Sections s ON t.SectionId = s.EntityId INNER JOIN tb_Groups g ON t.GroupId = g.EntityId WHERE s.Enabled = 1 AND g.Enabled = 1' + @dynamicConditions
	end
	else
	begin
		set @selectSql = N'SELECT TOP ' + CAST(@topCount AS NVARCHAR(32)) + ' ' + @selectTopFields + N' FROM tb_Threads t INNER JOIN tb_Sections s ON t.SectionId = s.EntityId INNER JOIN tb_Groups g ON t.GroupId = g.EntityId WHERE s.Enabled = 1 AND g.Enabled = 1 AND t.EntityId IN (SELECT p.EntityId FROM tb_Posts p WHERE p.ThreadId = @ReplierId)' + @dynamicConditions + ' ORDER BY ' + @orderFields
		set @selectCountSql = N'SELECT t.EntityId FROM tb_Threads t INNER JOIN tb_Sections s ON t.SectionId = s.EntityId INNER JOIN tb_Groups g ON t.GroupId = g.EntityId WHERE s.Enabled = 1 AND g.Enabled = 1 AND t.EntityId IN (SELECT p.EntityId FROM tb_Posts p WHERE p.ThreadId = @ReplierId)' + @dynamicConditions
		set @variableDeclarations = @variableDeclarations + N'DECLARE @ReplierId int; set @ReplierId = ' + CAST(@ReplierId AS NVARCHAR(32)) + N';'
		set @parameterString1 = @parameterString1 + N'@ReplierId int, '
		set @parameterString2 = @parameterString2 + N'@ReplierId, '
		set @parameterString3 = @parameterString3 + N',@ReplierId int'
		set @parameterString4 = @parameterString4 + N',@ReplierId'
	end
end
else
begin
	if @ReplierId is null or @ReplierId <= 0
	begin
		set @selectSql = N'SELECT TOP ' + CAST(@topCount AS NVARCHAR(32)) + ' ' + @selectTopFields + N' FROM tb_Threads t WHERE SectionId = @SectionId' + @dynamicConditions + ' ORDER BY ' + @orderFields
		set @selectCountSql = N'SELECT t.EntityId FROM tb_Threads t WHERE SectionId = @SectionId' + @dynamicConditions
		set @variableDeclarations = @variableDeclarations + N'DECLARE @SectionId int; set @SectionId = ' + CAST(@SectionId AS NVARCHAR(32)) + N';'		
		set @parameterString1 = @parameterString1 + N'@SectionId int, '
		set @parameterString2 = @parameterString2 + N'@SectionId, '
		set @parameterString3 = @parameterString3 + N',@SectionId int'
		set @parameterString4 = @parameterString4 + N',@SectionId'
	end
	else
	begin
		set @selectSql = N'SELECT TOP ' + CAST(@topCount AS NVARCHAR(32)) + ' ' + @selectTopFields + N' FROM tb_Threads t WHERE SectionId = @SectionId and t.EntityId IN (SELECT p.EntityId FROM tb_Posts p WHERE p.ThreadId = @ReplierId)' + @dynamicConditions + ' ORDER BY ' + @orderFields
		set @selectCountSql = N'SELECT t.EntityId FROM tb_Threads t WHERE SectionId = @SectionId and t.EntityId IN (SELECT p.EntityId FROM tb_Posts p WHERE p.ThreadId = @ReplierId)' + @dynamicConditions
		set @variableDeclarations = @variableDeclarations + N'DECLARE @SectionId int; set @SectionId = ' + CAST(@SectionId AS NVARCHAR(32)) + N';'
		set @parameterString1 = @parameterString1 + N'@SectionId int, '
		set @parameterString2 = @parameterString2 + N'@SectionId, '
		set @parameterString3 = @parameterString3 + N',@SectionId int'
		set @parameterString4 = @parameterString4 + N',@SectionId'
		set @variableDeclarations = @variableDeclarations + N'DECLARE @ReplierId int; set @ReplierId = ' + CAST(@ReplierId AS NVARCHAR(32)) + N';'
		set @parameterString1 = @parameterString1 + N'@ReplierId int, '
		set @parameterString2 = @parameterString2 + N'@ReplierId, '
		set @parameterString3 = @parameterString3 + N',@ReplierId int'
		set @parameterString4 = @parameterString4 + N',@ReplierId'
	end
end

-- Format @parameterString3 and @parameterString4
if @parameterString3 != N''
    set @parameterString3 = N', N''' + substring(@parameterString3, 2, len(@parameterString3) - 1) + N''', '
if @parameterString4 != N''
    set @parameterString4 = substring(@parameterString4, 2, len(@parameterString4) - 1)

-- Call sp_GetEntities to get the records of the current page and the total records count.
exec sp_GetEntities
@tableName,
@returnFields,
@PageSize,
@selectCountSql,
@selectSql,
@variableDeclarations,
@parameterString1,
@parameterString2,
@parameterString3,
@parameterString4,
@disOrderFields,
@TotalRecords output

GO